Sigh, I just realized after writing this post that I had already covered this topic... oh well, this version has some new information the other one is missing.

I find people very often asking me to move data from an Excel spreadsheet to a MySQL database, so I thought I’d write up the procedure I follow when doing so. This assumes no multi-line cells or tabs in the excel spreadsheet data.

1. You need a good text editor with regular expression support. I highly recommend EditPad Pro (a free version is available too), and will be assuming you are using it for the steps below.
2. Make sure all data in the Excel spreadsheet is formatted for SQL insertion, for example:
   To convert a date “mm/dd/yyyy” to SQL:
   1. Copy the entire row to your text editor
   2. Run the following regular expression replace:

      Find Text	Replace Text
      ^(\d+)/(\d+)/(\d+)$	$3-$1-$2

   3. Copy the text back to the spreadsheet row
3. Copy all the data into the text editor, and run the following regular expressions:

   Find Text	Replace Text	Explanation
   \\	\\\\	Escape backslash
   '	\\'	Escape single quotation mark
   \t	','	Change separators so that all values are encased as strings
   ^	('	Line prefix to insert a row and stringify the first value
   $	'),	Line suffix to insert a row and stringify the last value

4. Change the very last character on the last line from a comma to a semi colon to end the query
5. Add the following to the top of the file:

   SET NAMES 'utf8' COLLATE 'utf8_general_ci';
   SET CHARACTER SET 'utf8';
   TRUNCATE TABLE TABLE_NAME;
   INSERT INTO TABLE_NAME (Field1, Field2, ...) VALUES
   		

6. Make sure the file is saved as UTF8: Menu -> Convert -> Text Encoding -> (Encode the data with another character set ...) AND (Unicode, UTF-8)
7. Make sure the file is saved with Unix line breaks: Menu -> Convert -> To Unix (LF Only)
8. Save the file and run the following in your MySQL command line prompt to import it:

   \u DATABASE_NAME
   \. FILE_NAME
   		

There are of course easier solutions, but they can often be buggy, and I figured this is a good primer on regular expressions and simple data manipulation :-)